import mysql.connector
import pandas as pd

# 开发环境数据库
cnx = mysql.connector.connect(user='yiban', password='YIban123456',
                              host='rm-bp1ek32iruuf1tp84mo.mysql.rds.aliyuncs.com',
                              port=3306,

                              database='yiban')
#
# #生产环境数据库
# cnx = mysql.connector.connect(user='greatyiban', password='7g0yV45lpkJ6R1YeWGaO',
#                               host='202.200.126.179',
#                               port=11123,
#                               database='yiban')

status = 1  # 数据状态
campus_id = 0  # 0 曲江  1 金花

cursor = cnx.cursor()
def update_personal_info():
    for index, row in df.iterrows():
        ## yiban> INSERT INTO yiban.user_tbl_personal_info (col_name, col_sex, col_personal_id, col_password, col_applet_open_id, col_open_id, col_union_id, col_person_type, col_authority, col_update_time, col_avatar, col_credits, col_black, col_phone) VALUES ('测试', 1, '4234312', null, null, null, null, 10, null, DEFAULT, null, DEFAULT, DEFAULT, null)
        name = row[6]
        sex = 1
        personal_id  = int(row[7])
        person_type = 2
        password = row[7]%1000000
        authority = 0
        phone = int(row[9])



        query = (
            "insert into user_tbl_personal_info (col_name, col_sex, col_personal_id, col_password, col_person_type, col_authority,col_phone) VALUES (%s, %s, %s, %s, 1024, 0, %s)")
        params = (name, sex, personal_id, password, phone)
        cursor.execute(query, params)
        print("插入了一条个人信息")
        print(params)

def get_id(studentId):
    query = "SELECT col_id FROM user_tbl_personal_info WHERE col_personal_id = %s"
    params = (studentId,)
    print(params)
    cursor.execute(query, params)
    result = cursor.fetchone()
    if result:
        return int(result[0])
    else:
        return None

def update_student_info():
    for index, row in df.iterrows():
        studentId = int(row[7])
        card = row[8]
        enrollmentId = ''
        college = "电气工程学院"
        major = "电气工程"
        class_name = row[5]
        id = get_id(studentId)
        if id:

            query = (
                "insert into user_tbl_student_info (col_person_id, col_personal_id, col_enrollment_id, col_college, col_major, col_class,col_id_card_number) VALUES (%s, %s, %s, %s, %s, %s, %s)")
            params = (id,
                      studentId, enrollmentId, college, major, class_name,card)
            cursor.execute(query, params)
            print("更新了一条学生信息")
            print(params)

file_path = r'/Users/yuwei/Downloads/test.xlsx'
df = pd.read_excel(file_path, engine='openpyxl', sheet_name=0)
##df.fillna(method='ffill', inplace=True)  # 使用前一个非空值进行填充
df.ffill(inplace=True)  # 使用前一个非空值进行填充
print(df.shape)
df.drop_duplicates(inplace=True)  # 删除重复行 只是将 上面补充的数据中冗余部分清理掉
##更新person_info
update_personal_info()
##更新student_info
update_student_info()
## 提交事务
cnx.commit()
cursor.close()
cnx.close()